﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL.Entity;


namespace DAL.Persistencia
{
    public class SBasesDAL
    {

        private NewTierraEntities Con;

        public SBasesDAL()
        {
            Con = new NewTierraEntities();
        }

        public void Salvar(S_Bases b)
        {
            try
            {
                Con.AddToS_Bases(b);
                Con.SaveChanges();
            }
            catch 
            {                
                throw;
            }
        }

        public S_Bases ObterPorId(int IdBase)
        {
            try
            {
                return Con.S_Bases.Where(b => b.Base_id == IdBase).SingleOrDefault();
            }
            catch 
            {
                throw;
            }
        }

        public void Excluir(S_Bases b)
        {
            try
            {
                Con.DeleteObject(b);
                Con.SaveChanges();
            }
            catch 
            {                
                throw;
            }
        }

        public void Atualizar(S_Bases novo)
        {
            try
            {
                S_Bases antigo = ObterPorId(novo.Base_id);

                antigo.Base_de = novo.Base_de;
                antigo.Base_ate = novo.Base_ate;

                Con.SaveChanges();
            }
            catch 
            {
                throw;
            }
        }

        public List<S_Bases> ListarTodosPorSupplier(int IdSupplier, int Index, int IdMercado, int IdBaseTarifaria, int IdMoeda, int IdTransporte, int IdTipoBase)
        {
            try
            {
                return Con.S_Bases.Where(b => b.S_id        == IdSupplier 
                                      && b.Base_index       == Index
                                      && b.Mercado_id       == IdMercado
                                      && b.BaseTarifaria_id == IdBaseTarifaria
                                      && b.Moeda_id         == IdMoeda
                                      && b.IdTipoTrans      == IdTransporte
                                      && b.TipoBase_id      == IdTipoBase).OrderBy(b => b.Base_de).ToList();
            }
            catch 
            {                
                throw;
            }
        }

        public List<S_Bases> ListarTodosPorSupplier(int IdSupplier, int Index, int IdMercado, int IdBaseTarifaria, int IdMoeda, int IdTipoBase)
        {
            try
            {
                return Con.S_Bases.Where(b => b.S_id == IdSupplier
                                      && b.Base_index == Index
                                      && b.Mercado_id == IdMercado
                                      && b.BaseTarifaria_id == IdBaseTarifaria
                                      && b.Moeda_id == IdMoeda
                                      && b.TipoBase_id == IdTipoBase).OrderBy(b => b.Base_de).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<S_Bases> ObterPorIdSupplier(int IdSupplier)
        {
            try
            {
                //var query = from sb in Con.S_Bases
                //join bv in Con.S_Bases_Valor on sb.Base_id equals bv.Base_id                            

                return Con.S_Bases.Where(s => s.S_id == IdSupplier).ToList();

            }
            catch 
            {                
                throw;
            }
        }

        public List<S_Bases> ObterPorIdSupplier(int IdSupplier, int IdMercado)
        {
            try
            {              

                return Con.S_Bases.Where(s => s.S_id == IdSupplier && s.Mercado_id == IdMercado).ToList();

            }
            catch
            {
                throw;
            }
        }

        public List<S_Bases> ObterPorIdSupplierIndex(int IdSupplier, int index)
        {
            try
            {
                return Con.S_Bases.Where(b => b.S_id == IdSupplier &&
                                              b.Base_index == index).ToList();
            }
            catch 
            {                
                throw;
            }
        }

        public S_Bases ObterUmPorIdSupplierIndex(int IdSupplier, int index)
        {
            try
            {
                return Con.S_Bases.Where(b => b.S_id == IdSupplier &&
                                              b.Base_index == index).First();
            }
            catch
            {
                throw;
            }
        }

        public List<S_Bases> ObterPorIdSupplierDeAte(int IdSupplier, int de, int ate)
        {
            try
            {
                //WHERE     (S_id = '60792') AND (Base_de <= 1) AND (Base_ate >= 15) OR
                //      (S_id = '60792') AND (Base_de <= 15) AND (Base_ate >= 1)

                return Con.S_Bases.Where(s => s.S_id == IdSupplier && s.Base_de <= de && s.Base_ate >= ate ||
                                              s.S_id == IdSupplier && s.Base_de <= ate && s.Base_ate >= de).ToList();
            }
            catch 
            {                
                throw;
            }
        }

        public List<S_Bases> ObterPorIdSupplierDeAte(int IdSupplier, int de, int ate, int index)
        {
            try
            {                

                return Con.S_Bases.Where(s => s.S_id == IdSupplier && s.Base_de <= de && s.Base_ate >= ate && s.Base_index == index ||
                                              s.S_id == IdSupplier && s.Base_de <= ate && s.Base_ate >= de && s.Base_index == index).ToList();
            }
            catch 
            {                
                throw;
            }
        }
        
        public object ObterPorIdSupplierBase(int IdSupplier, int IdMServico, int index)
        {
            try
            {

                var query = from sb in Con.S_Bases
                            join bv in Con.S_Bases_Valor on sb.Base_id equals bv.Base_id into gj
                            from subbase in gj.DefaultIfEmpty()
                            where sb.S_id == IdSupplier &&
                                  subbase.IdMServico == IdMServico &&
                                  sb.Base_index == index
                                  orderby sb.Base_de
                            select new
                            {
                                sb.Base_id,
                                sb.Base_de,
                                sb.Base_ate
                            };


                return query.ToList();

                //return Con.S_Bases.Where(s => s.S_id == IdSupplier).ToList();

            }
            catch
            {
                throw;
            }
        }

        public object ObterPorIdSupplierBaseIndex(int IdSupplier, int index)
        {
            try
            {

                var query = from sb in Con.S_Bases
                            where sb.S_id == IdSupplier &&
                                  sb.Base_index == index
                            select new
                            {
                                sb.Base_id,
                                sb.Base_de,
                                sb.Base_ate                                
                            };


                return query.ToList();

                //return Con.S_Bases.Where(s => s.S_id == IdSupplier).ToList();

            }
            catch
            {
                throw;
            }
        }

        public bool VerificaIndex(int Index, int IdSupplier)
        {
            try
            {
                return Con.S_Bases.Where(s => s.Base_index == Index && s.S_id == IdSupplier).Count() != 0;
            }
            catch 
            {                
                throw;
            }
        }

        public bool VerificaExiste(int IdTransporte, int Index, int IdSupplier)
        {
            try
            {
                return Con.S_Bases.Where(s => s.IdTipoTrans == IdTransporte &&
                                              s.Base_index == Index &&
                                              s.S_id == IdSupplier).Count() != 0;
            }
            catch
            {
                throw;
            }
        }

    }
}
